package com.css.fxfzysx.modules.vabStructure.repository.impl;

import com.css.fxfzysx.constants.RelationConstants;
import com.css.fxfzysx.modules.cztDataMange.entity.CbvInfoCurveValueEntity;
import com.css.fxfzysx.modules.cztDataMange.entity.CbvInfoEntity;
import com.css.fxfzysx.modules.cztDataMange.entity.CbvInfoMatrixValueEntity;
import com.css.fxfzysx.modules.vabStructure.param.CustomVulnerabilityParam;
import com.css.fxfzysx.modules.vabStructure.param.MatchingParam;
import com.css.fxfzysx.modules.vabStructure.repository.CustomVulnerabilityRepository;
import com.css.fxfzysx.util.CreateGson;
import com.css.fxfzysx.util.PlatformObjectUtils;
import com.css.fxfzysx.zcpt.sys.entity.SUser;
import com.css.fxfzysx.zcpt.sys.service.SUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;

@Repository
public class CustomVulnerabilityRepositoryImpl implements CustomVulnerabilityRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private SUserService userService;

    @Override
    public HashMap<String, Object> queryData(CbvInfoEntity cbvInfoEntity, int page, int size) {
        StringBuilder count = new StringBuilder("select count(cbvi_id)");
        StringBuilder sb = new StringBuilder("select cbvi_id,number,province,city,county,structure_type,structure_type1,years,fortification,use0,use1,cs,create_user,create_time,basic_library");
        StringBuilder sql = new StringBuilder("  from "+RelationConstants.getConstant(cbvInfoEntity.getProvince())+" where del_flag='0'");
        if (!PlatformObjectUtils.isEmpty(cbvInfoEntity.getProvince())) {
            sql.append(" and province ='" + cbvInfoEntity.getProvince() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbvInfoEntity.getCity())) {
            sql.append(" and city ='" + cbvInfoEntity.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbvInfoEntity.getCounty())) {
            sql.append(" and county ='" + cbvInfoEntity.getCounty() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbvInfoEntity.getYears())) {
            sql.append(" and years ='" + cbvInfoEntity.getYears() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbvInfoEntity.getCs())) {
            sql.append(" and cs ='" + cbvInfoEntity.getCs() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbvInfoEntity.getCreateStartTime())) {
            sql.append(" and create_time >= '" + cbvInfoEntity.getCreateStartTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbvInfoEntity.getCreateEndTime())) {
            String endTime = cbvInfoEntity.getCreateEndTime() + " 23:59:59";
            sql.append(" and create_time <='" + endTime+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbvInfoEntity.getStructureType())) {
            sql.append(" and structure_type ='" + cbvInfoEntity.getStructureType()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbvInfoEntity.getStructureType1())) {
            sql.append(" and structure_type1 ='" + cbvInfoEntity.getStructureType1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbvInfoEntity.getFortification())) {
            sql.append(" and fortification ='" + cbvInfoEntity.getFortification()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbvInfoEntity.getUse0())) {
            sql.append(" and use0 ='" + cbvInfoEntity.getUse0()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbvInfoEntity.getUse1())) {
            sql.append(" and use1 ='" + cbvInfoEntity.getUse1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbvInfoEntity.getBasicLibrary())) {
            sql.append(" and basic_library ='" + cbvInfoEntity.getBasicLibrary()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbvInfoEntity.getUserName())) {
            sql.append(" and user_name like'%" + cbvInfoEntity.getUserName()+"%'");
        }
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        Integer total = jdbcTemplate.queryForObject(count.append(sql).toString(),Integer.class);
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        sb.append(sql).append(orderSql).append(limitSql);
        List<CbvInfoEntity> data = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CbvInfoEntity.class));
        data.parallelStream().forEach(it->{
            if (PlatformObjectUtils.isNotEmpty(it.getCreateUser())) {
                String user = userService.getSUser(it.getCreateUser());
                SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
                it.setUserName(sUser.getUserName());
            }
            if (PlatformObjectUtils.isNotEmpty(it.getProvince()) && PlatformObjectUtils.isNotEmpty(it.getCity()) && PlatformObjectUtils.isNotEmpty(it.getCounty())) {
                StringBuilder region = new StringBuilder(it.getProvince()).append("-").append(it.getCity()).append("-").append(it.getCounty());
                it.setRegion(region.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(it.getStructureType())) {
                StringBuilder st = new StringBuilder(it.getStructureType());
                if(PlatformObjectUtils.isNotEmpty(it.getStructureType1())){
                    st.append("-"+it.getStructureType1());
                }
                it.setSt(st.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(it.getUse0()) && PlatformObjectUtils.isNotEmpty(it.getUse0())) {
                StringBuilder use = new StringBuilder(it.getUse0()).append("-").append(it.getUse0());
                it.setUse(use.toString());
            }
        });
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", data);
        return resultMap;
    }

    @Override
    public void moveBasicLibrary(String id, String operate,String province) {
        String sql = "update "+RelationConstants.getConstant(province)+" set basic_library= ? where cbvi_id = ?";
        jdbcTemplate.update(sql,operate,id);
    }

    @Override
    public CbvInfoEntity getBasicLibraryById(String id,String province) {
        try {
            String sql = "select * from "+RelationConstants.getConstant(province)+" where cbvi_id ='" + id + "'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(CbvInfoEntity.class));
        }catch (Exception e){
            return null;
        }
    }

    @Override
    public Integer queryBasicLibrary(CustomVulnerabilityParam param) {
        try {
            String sql = "SELECT COUNT( *) FROM ( SELECT NUMBER , cpvi_id AS pid, province, city, county, structure_type0 AS structure_type, structure_type1, years, fortification, purpose0 AS use0, purpose1 AS use1, create_user, create_time, cs, basic_library, del_flag, data_type FROM czt_probability_vulnerability_info UNION ALL SELECT NUMBER , chbpi_id AS pid, province, city, county, structure_type AS structure_type, structure_type1, years, fortification, purpose AS use0, purpose1 AS use1, create_user, create_time, cs, basic_library, del_flag, data_type FROM czt_high_building_param_info UNION ALL SELECT NUMBER , cal_id AS pid, province, city, county, structure_type AS structure_type, structure_type1, years, fortification, purpose AS use0, purpose1 AS use1, create_user_id AS create_user, create_time, cs, basic_library, del_flag, data_type FROM czt_crs_calentirety_result UNION ALL SELECT NUMBER , cbcbi_id AS pid, province, city, county, structure_type AS structure_type, structure_type1, years, fortification, purpose AS use0, purpose1 AS use1, create_user, create_time, FLOOR AS cs, basic_library, del_flag, data_type FROM czt_brick_concrete_building_info UNION ALL SELECT NUMBER , matrix_id AS pid, province, city, county, structure_type, structure_type1, years, fortification, use0, use1, create_user, create_time, cs, basic_library, del_flag, data_type FROM czt_building_matrix UNION ALL SELECT NUMBER , cbvi_id AS pid, province, city, county, structure_type, structure_type1, years, fortification, use0, use1, create_user, create_time, cs, basic_library, del_flag, data_type FROM "+RelationConstants.getConstant(param.getProvince())+" ) result_Data  WHERE del_flag = '0' AND basic_library = '1' and province = '"+param.getProvince()+"' and " +
                    "city = '"+param.getCity()+"' and county = '"+param.getCounty()+"' and structure_type = '"+param.getStructureType()+"' " +
                    "and structure_type1 = '"+param.getStructureType1()+"' and years = '"+param.getYears()+"' " +
                    "and fortification = '"+param.getFortification()+"' and use0 = '"+param.getUse0()+"' " +
                    "and use1 = '"+param.getUse1()+"' and cs = '"+param.getCs()+"'";
            return jdbcTemplate.queryForObject(sql,Integer.class);
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public List<CbvInfoCurveValueEntity> getCureByCbviId(String cbviId) {
        String sql = "select * from czt_building_vulnerability_info_curve_value where cbvi_id='"+cbviId+"' order by sum";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(CbvInfoCurveValueEntity.class));
    }

    @Override
    public List<CbvInfoMatrixValueEntity> getMatrixByCbvId(String cbviId) {
        String sql = "select * from czt_building_vulnerability_info_matrix_value where cbvi_id='"+cbviId+"' order by create_time ";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(CbvInfoMatrixValueEntity.class));
    }


    @Override
    public List<CbvInfoEntity> queryBasicLibraryData(MatchingParam param) {
        StringBuilder sql = new StringBuilder("SELECT * FROM( SELECT cpvi_id AS pid, number, province, city, county, structure_type0 AS structure_type, " +
                "structure_type1, years, fortification, purpose0, purpose1, create_time, cs, basic_library, del_flag, " +
                "data_type FROM czt_probability_vulnerability_info UNION ALL SELECT matrix_id AS pid, number, province, " +
                "city, county, structure_type, structure_type1, years, fortification, use0 AS purpose0, use1 AS purpose1, " +
                "create_time, cs, basic_library, del_flag, data_type FROM czt_building_matrix UNION ALL SELECT cbvi_id AS pid, " +
                "number, province, city, county, structure_type, structure_type1, years, fortification, use0 AS purpose0, " +
                "use1 AS purpose1, create_time, cs, basic_library, del_flag, data_type FROM "+ RelationConstants.getConstant(param.getProvince())+") " +
                "result_Data WHERE del_flag = '0' AND basic_library = '1'");

        if (!PlatformObjectUtils.isEmpty(param.getProvince())) {
            sql.append(" and province ='" + param.getProvince() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getCity())) {
            sql.append(" and city ='" + param.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getCounty())) {
            sql.append(" and county ='" + param.getCounty() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getYears())) {
            sql.append(" and years ='" + param.getYears() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getStructureType())) {
            sql.append(" and structure_type ='" + param.getStructureType()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getStructureType1())) {
            sql.append(" and structure_type1 ='" + param.getStructureType1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getFortification())) {
            sql.append(" and fortification ='" + param.getFortification()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getUse0())) {
            sql.append(" and purpose0 ='" + param.getUse0()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getUse1())) {
            sql.append(" and purpose1 ='" + param.getUse1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getCs())) {
            sql.append(" and cs ='" + param.getCs()+"'");
        }
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CbvInfoEntity.class));
    }
}
